USE master
GO

IF EXISTS(SELECT name FROM sys.databases
WHERE name = 'EasyTicket')
DROP DATABASE EasyTicket
GO

create database EasyTicket
go

use EasyTicket


/*================================================================================*/

create table Item
(
ItemID int identity(1,1) not null,
PlaceID int not null,
ItemName nvarchar(50) not null,
Categories nvarchar(50) not null,
FromDate nvarchar(50) ,
ToDate nvarchar(50),
Decription nvarchar(50),
[Status] nvarchar(10),
[Image] nvarchar(50) 
)
go

alter table Item
add constraint pk_ItemID primary key nonclustered(ItemID)
go



create table Places
(
PlaceID int identity(1,1) not null,
PlaceName nvarchar(50) not null,
[Status] nvarchar(10),
Decristion nvarchar(100) 
)
go

alter table Places
add constraint pk_PlaceID primary key nonclustered(PlaceID)
go


create table OrderDetail
(
OrderID int not null,
TicketID int not null,
Quantity int not null
 CONSTRAINT [PK_OrderDetail] PRIMARY KEY CLUSTERED 
(
	[OrderID] ASC,
	[TicketID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
go



create table Ticket
(
TicketID int identity(1,1) not null,
PlaceID int not null,
EventID int not null,
TicketName nvarchar(50)  not null,
Total nvarchar(50) not null,
[DateTime] nvarchar(50) ,
Quantity nvarchar(10),
Decription nvarchar(50),
[Status] nvarchar(10),
Price nvarchar(50) 
)
go

alter table Ticket
add constraint pk_TicketID primary key nonclustered(TicketID)
go

create table TicketReport
(
ID int identity(1,1) not null,
TicketID int not null,
Total int not null,
[DateTime] date 
)
go

alter table TicketReport
add constraint pk_ID primary key nonclustered(ID)
go

create table Users
(
UserID int identity(1,1) not null,
Username nvarchar(50) not null,
[Password] nvarchar(50) not null,
Name nvarchar(50) not null,
DayOfBirth nvarchar(50) ,
[Address] nvarchar(100),
Phone nvarchar(50) ,
Email nvarchar(50),
[Role] nvarchar(10),
[Status] nvarchar(10)
)
go

create table UserReport
(
ID int identity(1,1) not null,
UserID int not null,
[DateTime] date,
Total int not null
)
go

alter table UserReport
add constraint pk_UID primary key nonclustered(ID)
go

alter table Users
add constraint pk_UserID primary key nonclustered(UserID)
go


create table Orders
(
OrderID int identity(1,1) not null,
UserID int not null,
CardNumber nvarchar(50) not null,
TotalPrice nvarchar(50),
[Date] nvarchar(50),
[Address] nvarchar(50),
[Status] nvarchar(10)
)
go

alter table Orders
add constraint pk_OrderID primary key nonclustered(OrderID)
go

create table Faqs
(
FaqID int identity(1,1) not null,
FaqName nvarchar(50) not null,
Question nvarchar(500) ,
Answer nvarchar(500) ,
[Status] nvarchar(10)
)
go

alter table Faqs
add constraint pk_FaqID primary key nonclustered(FaqID)
go

create table [Event]
(
EventID int identity(1,1) not null,
EventName nvarchar(500) not null,
Decripstion nvarchar(500) not null,
Discount nvarchar(50) ,
FromDate date,
ToDate date,
[Status] nvarchar(10)
)
go

alter table [Event]
add constraint pk_EventID primary key nonclustered(EventID)
go

create table CreditCard
(

CardNumber nvarchar(50) not null,
Balance int not null,
[Status] int
)
go

alter table CreditCard
add constraint pk_CardID primary key nonclustered(CardNumber)
go


alter table Item
add constraint fk_Place foreign key(PlaceID) references Places(PlaceID) ON DELETE CASCADE
go

alter table Ticket
add constraint fk_Place1 foreign key(PlaceID) references Places(PlaceID) ON DELETE CASCADE
go

alter table Ticket
add constraint fk_Event foreign key(EventID) references [Event](EventID) ON DELETE CASCADE
go

alter table TicketReport
add constraint fk_Report foreign key(TicketID) references Ticket(TicketID) ON DELETE CASCADE
go


alter table [Orders]
add constraint fk_User foreign key(UserID) references Users(UserID) ON DELETE CASCADE
go

alter table [Orders]
add constraint fk_Card foreign key(CardNumber) references CreditCard(CardNumber) ON DELETE CASCADE
go

alter table UserReport
add constraint fk_UReport foreign key(UserID) references Users(UserID) ON DELETE CASCADE
go

ALTER TABLE [dbo].[OrderDetail]  WITH CHECK ADD  CONSTRAINT [FK_OrderDetail_Orders] FOREIGN KEY([OrderID])
REFERENCES [dbo].[Orders] ([OrderID])
GO
ALTER TABLE [dbo].[OrderDetail] CHECK CONSTRAINT [FK_OrderDetail_Orders]
GO
/****** Object:  ForeignKey [FK_OrderDetail_Ticket]    Script Date: 04/21/2013 20:38:10 ******/
ALTER TABLE [dbo].[OrderDetail]  WITH CHECK ADD  CONSTRAINT [FK_OrderDetail_Ticket] FOREIGN KEY([TicketID])
REFERENCES [dbo].[Ticket] ([TicketID])
GO
ALTER TABLE [dbo].[OrderDetail] CHECK CONSTRAINT [FK_OrderDetail_Ticket]
GO